package com.atguigu.scheduler.job;

import com.atguigu.scheduler.bean.MonitorDetail;
import org.quartz.*;
import org.quartz.impl.StdSchedulerFactory;

import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * 1.DAG任务的调度  airflow / dolphin schedule
 * 2.定时任务  -cron任务  -周期性任务
 * java底层的调度器quartz
 * <p>
 * 自定义job
 */
public class MySQLMonitorJob implements Job {
    public MySQLMonitorJob() {
    }

    @Override
    public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {
        //获取传入的参数字典
        var dataMap = jobExecutionContext.getJobDetail().getJobDataMap();

        //取出参数
        var databaseName = dataMap.getString("databaseName");
        var tableName = dataMap.getString("tableName");
        var fieldName = dataMap.getString("fieldName");

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            var connection = DriverManager.getConnection(
                    "jdbc:mysql://172.31.15.7:3306/" + databaseName + "?useSSL=false",
                    "root",
                    "Xnetworks@c0M"
            );

            var monitorDetail = new MonitorDetail();
            monitorDetail.setDatabaseName(databaseName);
            monitorDetail.setTableName(tableName);
            monitorDetail.setFieldName(fieldName);

            //1.计算gmall.yuntai_zuoyuan表非空值率
            var selectNullRate = connection.prepareStatement(
                    "SELECT" +
                            "   count(1) as TotalAll," +
                            "   count(" + fieldName + ") as TotalNotNull," +
                            "   count(1) - count(" + fieldName + ") as TotalNull," +
                            "   100.0 * count(" + fieldName + ") / count(1) as PercentNotNull" +
                            "  FROM " + tableName
            );
            var resultSet = selectNullRate.executeQuery();
            if (resultSet.next()) {
                monitorDetail.setFieldNullRate(resultSet.getDouble("PercentNotNull"));
            }

            //2.将监控结果写入mysql_data_monitor表
            var insertConnection = DriverManager.getConnection(
                    "jdbc:mysql://172.31.15.7:3306/yuntai_government?useSSL=false",
                    "root",
                    "Xnetworks@c0M"
            );

            var insertStatement = insertConnection.prepareStatement(
                    "INSERT INTO mysql_data_monitor (database_name, table_name, field_name, field_null_rate) VALUES (?, ?, ?, ?)"
            );

            insertStatement.setString(1, databaseName);
            insertStatement.setString(2, tableName);
            insertStatement.setString(3, fieldName);
            insertStatement.setDouble(4, monitorDetail.getFieldNullRate());
            insertStatement.execute();

            selectNullRate.close();
            insertStatement.close();
            insertConnection.close();
            connection.close();
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws SchedulerException {
        Scheduler scheduler = StdSchedulerFactory.getDefaultScheduler();
        scheduler.start();

        JobDetail job = JobBuilder
                .newJob(SimpleJob.class)
                .withIdentity("c","g")
                .build();

        CronTrigger cronTrigger = TriggerBuilder.newTrigger()
                .withIdentity("trigger1", "group").startNow()
                .withSchedule(CronScheduleBuilder.cronSchedule("* * * * * ? *")).build();

        scheduler.scheduleJob(job, cronTrigger);
    }
}
